package my.assistant.homecashmanager.inoutitem.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import my.assistant.homecashmanager.inoutitem.data.DmInoutItem;

import org.assistant.common.exception.GetFromDBException;
import org.assistant.common.exception.SaveIntoDBException;
import org.assistant.common.jdbc.core.RowMapper;
import org.assistant.common.jdbc.dao.BasicDao;

public class DmInoutItemDaoImpl extends BasicDao implements DmInoutItemDao {
	private class DmInoutItemRowMapper implements RowMapper<DmInoutItem> {
		public DmInoutItem mapRow(ResultSet rs, int index) throws SQLException {
			DmInoutItem item = new DmInoutItem();

			item.setId(rs.getLong("inout_item_id_"));
			item.setName(rs.getString("inout_item_name_"));
			item.setInouttype(rs.getString("inouttype_"));
			item.setExplain(rs.getString("explain_"));
			item.setParentid(rs.getLong("parent_id_"));
			item.setHomeId(rs.getString("home_id_"));
			item.setStatus(rs.getBoolean("status_"));
			item.setOpDate(rs.getDate("op_date_"));
			item.setCreater(rs.getString("creater_"));
			item.setCreateDate(rs.getDate("create_date_"));
			item.setLft(rs.getInt("lft_"));
			item.setRgt(rs.getInt("rgt_"));

			return item;
		}
	}

	/**
	 * 根据ID获取一条收支项目
	 * 
	 * @param id
	 * @return
	 * @throws GetFromDBException
	 */
	public DmInoutItem getDmInoutItemById(long id) throws GetFromDBException {
		String sql = "select a.inout_item_id_,a.inout_item_name_,a.inouttype_,a.explain_,a.parent_id_,a.home_id_,a.status_,a.op_date_,a.creater_,a.create_date_,a.lft_,a.rgt_ from dm_inout_item a where a.inout_item_id_=?";
		DmInoutItem item = null;
		try {
			item = getJdbcTemplate().queryForObject(
				sql, new DmInoutItemRowMapper(), id);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new GetFromDBException("查询收支项目时出错啦：" + e.getMessage());
		}
		return item;
	}

	/**
	 * 根据homeId得到该家庭所拥有的收支项目列表（包括系统级项目）
	 * 
	 * @param homeId
	 * @return
	 * @throws GetFromDBException
	 */
	public List<DmInoutItem> getDmInoutItemByHome(String homeId)
			throws GetFromDBException {
		String sql = "select a.inout_item_id_,a.inout_item_name_,a.inouttype_,a.explain_,a.parent_id_,a.home_id_,a.status_,a.op_date_,a.creater_,a.create_date_,a.lft_,a.rgt_ from dm_inout_item a where (a.home_id_='111111' or a.home_id_=?) and a.status_=true";

		List<DmInoutItem> itemList = null;
		try {
			itemList = getJdbcTemplate().queryForList(
				sql, new DmInoutItemRowMapper(), homeId);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new GetFromDBException("查询收支项目时出错啦：" + e.getMessage());
		}

		return itemList;
	}

	/**
	 * 往数据库中新插入一条收支项目
	 * 
	 * @param item
	 * @return
	 * @throws SaveIntoDBException
	 */
	public void insertDmInoutItem(DmInoutItem item) throws SaveIntoDBException {
		StringBuffer sqlBuff = new StringBuffer("insert dm_inout_item(");
		sqlBuff
			.append("inout_item_id_,inout_item_name_,inouttype_,explain_,parent_id_,home_id_,");
		sqlBuff.append("status_,op_date_,creater_,create_date_) values(");
		sqlBuff.append("null,?,?,?,?,?,'01',now(),");
		sqlBuff.append("?,now())");

		int rows;
		try {
			rows = getJdbcTemplate().update(
				sqlBuff.toString(), item.getName(), item.getInouttype(),
				item.getExplain(), item.getParentid(), item.getHomeId(),
				item.getCreater());
		} catch (SQLException e) {
			e.printStackTrace();
			throw new SaveIntoDBException("保存收支项目时出错啦：" + e.getMessage());
		}

		if (rows <= 0) {
			throw new SaveIntoDBException("保存收支项目时出错啦！");
		}
	}

	/**
	 * 修改一条收支项目
	 * 
	 * @param item
	 * @throws SaveIntoDBException
	 */
	public void updateDmInoutItem(DmInoutItem item) throws SaveIntoDBException {
		StringBuffer sqlBuff = new StringBuffer("update dm_inout_item set ");
		sqlBuff
			.append("inout_item_name_=?,explain_=?,parent_id_=?,op_date_=now() ");
		sqlBuff.append("where inout_item_id_=?");

		int rows;
		try {
			rows = getJdbcTemplate().update(
				sqlBuff.toString(), item.getName(), item.getExplain(),
				item.getParentid(), item.getId());
		} catch (SQLException e) {
			e.printStackTrace();
			throw new SaveIntoDBException("修改收支项目时出错啦：" + e.getMessage());
		}

		if (rows <= 0) {
			throw new SaveIntoDBException("修改收支项目时出错啦！");
		}
	}

	/**
	 * 禁用一条收支项目
	 * 
	 * @param id
	 * @throws SaveIntoDBException
	 */
	public void disableDmInoutItem(long id) throws SaveIntoDBException {
		StringBuffer sqlBuff = new StringBuffer("update dm_inout_item set ");
		sqlBuff.append("status_=false ");
		sqlBuff.append("where inout_item_id_=?");

		int rows;
		try {
			rows = getJdbcTemplate().update(sqlBuff.toString(), id);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new SaveIntoDBException("禁用收支项目时出错啦：" + e.getMessage());
		}

		if (rows <= 0) {
			throw new SaveIntoDBException("禁用收支项目时出错啦！");
		}
	}
}
